import time
import pymysql
import json
import traceback
import requests




def get_tencent_data():
    url1 = 'https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5'
    url2 = 'https://view.inews.qq.com/g2/getOnsInfo?name=disease_other'
    headers = {
        "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.122 Safari/537.36"
    }
    r1 = requests.get(url1, headers)
    r2 = requests.get(url2, headers)

    res1 = json.loads(r1.text)
    res2 = json.loads(r2.text)

    data_all1 = json.loads(res1["data"])
    data_all2 = json.loads(res2["data"])
    # print(data_all2.keys())
    history = {}
    for i in data_all2["chinaDayList"]:
        ds = "2021." + i["date"]
        tup = time.strptime(ds, "%Y.%m.%d")  # 匹配时间
        ds = time.strftime("%Y-%m-%d", tup)  # 改变时间格式
        confirm = i["confirm"]
        suspect = i["suspect"]
        heal = i["heal"]
        dead = i["dead"]
        history[ds] = {"confirm": confirm, "suspect": suspect, "heal": heal, "dead": dead}
    for i in data_all2["chinaDayAddList"]:
        ds = "2021." + i["date"]
        tup = time.strptime(ds, "%Y.%m.%d")  # 匹配时间
        ds = time.strftime("%Y-%m-%d", tup)  # 改变时间格式
        confirm = i["confirm"]
        suspect = i["suspect"]
        heal = i["heal"]
        dead = i["dead"]
        history[ds].update({"confirm_add": confirm, "suspect_add": suspect, "heal_add": heal, "dead_add": dead})

    details = []

    update_time = data_all1["lastUpdateTime"]
    data_country = data_all1["areaTree"]
    data_province = data_country[0]["children"]

    for pro_infos in data_province:
        province = pro_infos["name"]
        for city_infos in pro_infos["children"]:
            city = city_infos["name"]
            confirm = city_infos["total"]["confirm"]
            confirm_add = city_infos["today"]["confirm"]
            heal = city_infos["total"]["heal"]
            dead = city_infos["total"]["dead"]
            details.append([update_time, province, city, confirm, confirm_add, heal, dead])

    chinaTotals = []
    chinaTotal = data_all1["chinaTotal"]
    # 累计确诊数
    all_Confirm = chinaTotal["confirm"]
    # 现有确诊
    all_NowConfirm = chinaTotal["nowConfirm"]
    # 死治愈数
    all_Heal = chinaTotal["heal"]
    # 死死亡数
    all_Dead = chinaTotal["dead"]
    # 疑似病例
    now_suspect = chinaTotal["suspect"]
    chinaTotals.append([all_Confirm,all_NowConfirm,all_Heal,all_Dead,now_suspect,update_time])

    return history, details,chinaTotals[0]





def get_conn():
    # 建立连接
    conn = pymysql.connect(host="localhost", user="root", password="123456", db="cov", charset="utf8")
    # c创建游标
    cursor = conn.cursor()
    return conn, cursor


def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()


#定义更新细节函数
def update_details():
    cursor = None
    conn = None
    try:
        li = get_tencent_data()[1]#1代表最新数据
        chinaTotal = get_tencent_data()[2]
        conn,cursor = get_conn()
        sql = "insert into details(update_time,province,city,confirm,confirm_add,heal,dead) values(%s,%s,%s,%s,%s,%s,%s)"
        # sql_query = 'select %s=(select update_time from details order by id desc limit 1)' #根据ID倒序查询最后一次更新时间 判断是否相等
        sql_query2 = "SELECT COUNT(1) FROM details WHERE update_time >= %s"
        #对比当前最大时间戳
        sql2 = "insert into chinatotal(confirm,nowConfirm,heal,dead,suspect,update_time) values(%s,%s,%s,%s,%s,%s) "
        cursor.execute(sql_query2,li[0][0])
        results = cursor.fetchone()
        if not results[0]>1:
            print(f"{time.asctime()}开始更新数据")
            for item in li:
                cursor.execute(sql,item)
            cursor.execute(sql2,chinaTotal)
            conn.commit()
            print(f"{time.asctime()}更新到最新数据")
        else:
            print(f"{time.asctime()}已是最新数据！")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn,cursor)


#插入历史数据
def insert_history():
    cursor = None
    conn = None
    try:
        dic = get_tencent_data()[0]#0代表历史数据字典
        print(f"{time.asctime()}开始插入历史数据")
        conn,cursor = get_conn()
        sql = "insert into history values (%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        for k,v in dic.items():
            cursor.execute(sql,[k, v.get("confirm"),v.get("confirm_add"),v.get("suspect"),
                           v.get("suspect_add"),v.get("heal"),v.get("heal_add"),
                           v.get("dead"),v.get("dead_add")])
        conn.commit()
        print(f"{time.asctime()}插入历史数据完毕")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn,cursor)


#更新历史数据
def update_history():
    cursor = None
    conn = None
    try:
        dic = get_tencent_data()[0]#0代表历史数据字典
        print(f"{time.asctime()}开始更新历史数据")
        conn,cursor = get_conn()
        sql = "insert into history values (%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = "select confirm from history where ds=%s"
        for k,v in dic.items():
            if not cursor.execute(sql_query,k):
                cursor.execute(sql,[k, v.get("confirm"),v.get("confirm_add"),v.get("suspect"),
                               v.get("suspect_add"),v.get("heal"),v.get("heal_add"),
                               v.get("dead"),v.get("dead_add")])
        conn.commit()
        print(f"{time.asctime()}历史数据更新完毕")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn,cursor)



#爬取百度热搜数据
# def get_baidu_hot():
#     option = ChromeOptions()
#     option.add_argument("--headless")#隐藏游览器
#     option.add_argument("--no--sandbox")
#     browser =  Chrome(options = option,executable_path="chromedriver-dev.exe")
#
#     url = "https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5"
#     browser.get(url)
#     #print(brower.page_source)
#     but = browser.find_element_by_css_selector('#ptab-0 > div > div.VirusHot_1-5-3_32AY4F.VirusHot_1-5-3_2RnRvg > section > div')
#     #点击加载更多
#     but.click()
#     time.sleep(1)
#     #爬虫与反爬，模拟人等待1秒
#     c = browser.find_elements_by_xpath('//*[@id="ptab-0"]/div/div[2]/section/a/div/span[2]')
#     context = [i.text for i in c]
#     browser.close()
#     return context


# def update_hotsearch():
#     cursor = None
#     conn = None
#     try:
#         context = get_baidu_hot()
#         print(f"{time.asctime()}开始更新数据")
#         conn,cursor = get_conn()
#         sql = "insert into hotsearch(dt,content) values(%s,%s)"
#         ts = time.strftime("%Y-%m-%d %X")
#         for i in context:
#             cursor.execute(sql,(ts,i))
#         conn.commit()
#         print(f"{time.asctime()}数据更新完毕")
#     except:
#         traceback.print_exc()
#     finally:
#         close_conn(conn,cursor)

if __name__ == "__main__":
    # data1,data2,data3 = get_tencent_data()
    # print(data3[0])
    update_details()
    update_history()

